package com.roadjava.javaweb.student.dao.impl;

import com.roadjava.javaweb.student.beans.entity.ScoreDO;
import com.roadjava.javaweb.student.beans.req.StudentRequest;
import com.roadjava.javaweb.student.beans.res.TableResult;
import com.roadjava.javaweb.student.beans.vo.ScoreVO;
import com.roadjava.javaweb.student.dao.ScoreDao;
import com.roadjava.javaweb.student.util.DBUtil;
import com.roadjava.javaweb.student.util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class ScoreDaoImpl implements ScoreDao {
    @Override
    public int addScore(ScoreDO scoreDO) {
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return 0;
        }
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        sb.append(" insert into score(student_id,cn_score,en_score,math_score) ");
        sb.append(" values(?,?,?,?) ");
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,scoreDO.getStudentId());
            ps.setObject(2,scoreDO.getCnScore());
            ps.setObject(3,scoreDO.getEnScore());
            ps.setObject(4,scoreDO.getMathScore());
            // 打印最终执行的sql语句
            System.out.println("addScore执行的sql:" + ps.toString());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return 0;
    }

    @Override
    public TableResult<ScoreVO> queryScoreByPage(StudentRequest studentRequest) {
        TableResult<ScoreVO> tableResult = new TableResult<>();
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return null;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        // 查询学生列表
        StringBuilder sb = new StringBuilder();
        sb.append(" select s1.*,s2.name,s2.no from score s1 ")
                .append(" left join student s2 on s1.student_id =s2.id ");
        appendWhere(studentRequest,sb);
        sb.append(" order by s1.id desc limit ").append(studentRequest.getStart())
                .append(",").append(studentRequest.getPageSize());
        try {
            ps = conn.prepareStatement(sb.toString());
            if (StringUtil.isNotBlank(studentRequest.getStudentName())) {
                ps.setObject(1,studentRequest.getStudentName());
            }
            // 打印最终执行的sql语句
            System.out.println("queryScoreByPage查询成绩的sql:" + ps.toString());
            rs = ps.executeQuery();
            List<ScoreVO> list = new ArrayList<>();
            while (rs.next()) {
                ScoreVO scoreVO = buildScore(rs);
                list.add(scoreVO);
            }
            tableResult.setData(list);// 设置数据
            /*
            查询总条数
             */
            sb.setLength(0);
            sb.append(" select count(*) from score s1 ")
                    .append(" left join student s2 on s1.student_id =s2.id ");
            appendWhere(studentRequest,sb);
            ps = conn.prepareStatement(sb.toString());
            if (StringUtil.isNotBlank(studentRequest.getStudentName())) {
                ps.setObject(1,studentRequest.getStudentName());
            }
            // 打印最终执行的sql语句
            System.out.println("queryScoreByPage查询成绩总条数的sql:" + ps.toString());
            rs = ps.executeQuery();
            if (rs.next()) {
                int totalCount = rs.getInt(1);
                tableResult.setTotalCount(totalCount); // 设置总条数
            }
            return tableResult;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public ScoreVO getScoreById(long scoreId) {
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return null;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        // 查询学生列表
        StringBuilder sb = new StringBuilder();
        sb.append(" select s1.*,s2.name,s2.no from score s1 ")
                .append(" left join student s2 on s1.student_id =s2.id ")
                .append(" where s1.id = ? ");
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,scoreId);
            // 打印最终执行的sql语句
            System.out.println("getScoreById查询成绩的sql:" + ps.toString());
            rs = ps.executeQuery();
            if (rs.next()) {
                return buildScore(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public int updateScore(ScoreDO scoreDO) {
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return 0;
        }
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        sb.append(" update score  set student_id = ?,cn_score = ?")
                .append(" ,en_score = ?,math_score = ? ")
                .append(" where id = ?");
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,scoreDO.getStudentId());
            ps.setObject(2,scoreDO.getCnScore());
            ps.setObject(3,scoreDO.getEnScore());
            ps.setObject(4,scoreDO.getMathScore());
            ps.setObject(5,scoreDO.getId());
            // 打印最终执行的sql语句
            System.out.println("updateScore执行的sql:" + ps.toString());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return 0;
    }

    @Override
    public int deleteScoreById(long scoreId) {
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return 0;
        }
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from  score  where id = ? ");
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,scoreId);
            // 打印最终执行的sql语句
            System.out.println("deleteScoreById执行的sql:" + ps.toString());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return 0;
    }

    private ScoreVO buildScore(ResultSet rs) throws SQLException {
        long id = rs.getLong("id");
        long student_id = rs.getLong("student_id");
        double cn_score = rs.getDouble("cn_score");
        double en_score = rs.getDouble("en_score");
        double math_score = rs.getDouble("math_score");
        String name = rs.getString("name");
        String no = rs.getString("no");
        ScoreVO scoreVO = new ScoreVO();
        scoreVO.setId(id);
        scoreVO.setStudentId(student_id);
        scoreVO.setCnScore(cn_score);
        scoreVO.setEnScore(en_score);
        scoreVO.setMathScore(math_score);
        scoreVO.setName(name);
        scoreVO.setNo(no);
        return scoreVO;
    }

    private void appendWhere(StudentRequest studentRequest, StringBuilder sb) {
        String studentName = studentRequest.getStudentName();
        if (StringUtil.isNotBlank(studentName)) {
            sb.append(" where s2.name = ? ");
        }
    }
}
